#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sqlite3
import urllib
import time
import threading
import sys

if sys.getdefaultencoding() != 'utf-8':
    reload(sys)
    sys.setdefaultencoding('utf-8')

class DbHelper:
    mDbPath=''

    '''
    CREATE TABLE `profile` 
    '''
    def __init__(self, dbpath):
        self.lock = threading.RLock() #锁  
        self.mDbPath = dbpath
        self.conn = sqlite3.connect(dbpath, check_same_thread=False)
        c = self.conn.cursor()
        
        c.execute('''CREATE TABLE if not exists "profile" ( `id` INTEGER, `profile` TEXT, `email` TEXT, `searchresult` TEXT, PRIMARY KEY(`email`) )''')
        print "Table created successfully"
        self.conn.commit()


    def insertALine(self, id, profile, email,searchresult):
        c = self.conn.cursor()
        email=email.replace("'", "''")
        profile=profile.replace("'", "''")
        searchresult=searchresult.replace("'", "''")
        sql="replace into profile (id, profile,email,searchresult) values('%d', '%s', '%s', '%s')" % (id, profile, email,searchresult.encode('utf8'))
        try:
            c.execute(sql)
        except sqlite3.IntegrityError as e:
            print(email)
            print(e)

    def getEmails(self, count):
        emails = []
        c = self.conn.cursor()
        try:
            cursor = c.execute("select email from profile where id=-2 limit %d" %(count))
            for row in cursor:
                emails.append(row[0])
        except sqlite3.IntegrityError as e:
            print(e)
        except sqlite3.OperationalError as e:
            print e
        return emails

    def commit(self):
        self.conn.commit()
    
    def importintodb(self, filepath):
        file = open(filepath) 
        while 1:
            lines = file.readlines(1000000)
            if not lines:
                break
            print(len(lines))
            for email in lines:
                email=email.strip()
                self.insertALine(-2, '', email, '')
            print "commit"
            self.commit()
        file.close()

if __name__ == "__main__":
    Tstdb = DbHelper('talkbox.db')
    Tstdb.importintodb('res.bcp')
    emails=Tstdb.getEmails(10)
    print(emails)

    #Tstdb.insertALine(1, "testcontent")
    #Tstdb.refreshpassword()
    #Tstdb.importnewssid(sys.argv[1])

